﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Adams.Admin.DataAccess.Businesses
{
    public static class Create
    {
        public static Adams.Admin.DataTransfer.Businesses.Create.PageLoad PageLoad(int id)
        {
            var data = new Adams.Admin.DataTransfer.Businesses.Create.PageLoad();
            data.Business = new Shared.Entities.Businesses.Business();
            data.Types = new Shared.Entities.Businesses.TypeCollection();
            data.Categories = new Shared.Entities.Businesses.CategoryCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
IF(@BusinessID > 0)
BEGIN
    DECLARE @TypeID int
    DECLARE @CategoryID int
    SELECT @CategoryID = CategoryID FROM Businesses.Businesses WHERE BusinessID = @BusinessID
    SELECT @TypeID = TypeID FROM Businesses.Categories WHERE CategoryID = @CategoryID

    SELECT P.BusinessID,P.CategoryID,P.MemberID,P.StatusID,P.Name,P.Street,P.Street2,P.City,P.State,P.Zip,P.Phone,P.Description,P.CreatedOn,P.ExpiresOn,M.FirstName,M.LastName,M.Email,T.TypeID
    FROM Businesses.Businesses P INNER JOIN Businesses.Categories C ON P.CategoryID = C.CategoryID INNER JOIN Businesses.Types T ON C.TypeID = T.TypeID INNER JOIN Membership.Members M ON P.MemberID = M.MemberID
    WHERE BusinessID = @BusinessID

    SELECT * FROM Businesses.Categories WHERE TypeID = @TypeID ORDER BY Name
END
SELECT * FROM Businesses.Types ORDER BY Name
", CommandType.Text,
            DB.CreateParameter("@BusinessID", id, SqlDbType.Int)))
            {
                if (id > 0)
                {
                    dr.Read();
                    data.Business.ID = dr["BusinessID"].DbInt();
                    data.Business.Status = (Shared.Entities.Businesses.Status)dr["StatusID"].DbInt();
                    data.Business.Name = dr["Name"].DbString();
                    data.Business.Street = dr["Street"].DbString();
                    data.Business.Street2 = dr["Street2"].DbString();
                    data.Business.City = dr["City"].DbString();
                    data.Business.State = dr["State"].DbString();
                    data.Business.Zip = dr["Zip"].DbString();
                    data.Business.Phone = dr["Phone"].DbString();
                    data.Business.Description = dr["Description"].DbString();
                    data.Business.CreatedOn = dr["CreatedOn"].DbDate();
                    data.Business.ExpiresOn = dr["ExpiresOn"].DbDate();
                    data.Business.Category = new Shared.Entities.Businesses.Category()
                    {
                        ID = dr["CategoryID"].DbInt(),
                        Type = new Shared.Entities.Businesses.Type()
                        {
                            ID = dr["TypeID"].DbInt()
                        }
                    };
                    data.Business.Member = new Shared.Entities.Membership.Member()
                    {
                        ID = dr["MemberID"].DbInt(),
                        Email = dr["Email"].DbString(),
                        FirstName = dr["FirstName"].DbString(),
                        LastName = dr["LastName"].DbString(),
                    };

                    dr.NextResult();
                    while (dr.Read())
                    {
                        var cat = new Shared.Entities.Businesses.Category();
                        cat.ID = dr["CategoryID"].DbInt();
                        cat.Name = dr["Name"].DbString();
                        data.Categories.Add(cat);
                    }
                    dr.NextResult();
                }
                while (dr.Read())
                {
                    var typ = new Shared.Entities.Businesses.Type();
                    typ.ID = dr["TypeID"].DbInt();
                    typ.Name = dr["Name"].DbString();
                    data.Types.Add(typ);
                }
            }

            return data;
        }

        public static Adams.Shared.Entities.Businesses.CategoryCollection GetCategoriesByType(int typeId)
        {
            var data = new Shared.Entities.Businesses.CategoryCollection();

            if (typeId > 0)
            {
                using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * FROM Businesses.Categories WHERE TypeID = @TypeID ORDER BY Name
", CommandType.Text,
                DB.CreateParameter("@TypeID", typeId, SqlDbType.Int)))
                {
                    while (dr.Read())
                    {
                        var cat = new Shared.Entities.Businesses.Category();
                        cat.ID = dr["CategoryID"].DbInt();
                        cat.Name = dr["Name"].DbString();
                        data.Add(cat);
                    }
                }
            }

            return data;
        }

        public static void Save(DataTransfer.Businesses.Create.FormInput input)
        {
            DB.Run(@"
IF(@BusinessID > 0)
BEGIN
    UPDATE Businesses.Businesses SET
        CategoryID = @CategoryID
        ,MemberID = @MemberID
        ,StatusID = @StatusID
        ,Name = @Name
        ,Street = @Street
        ,Street2 = @Street2
        ,City = @City
        ,State = @State
        ,Zip = @Zip
        ,Phone = @Phone
        ,Description = @Description
        ,ExpiresOn = @ExpiresOn
    WHERE BusinessID = @BusinessID
END
ELSE
BEGIN
    INSERT INTO Businesses.Businesses (
        CategoryID
        ,MemberID
        ,StatusID
        ,Name
        ,Street
        ,Street2
        ,City
        ,State
        ,Zip
        ,Phone
        ,Description
        ,ExpiresOn)
    VALUES (
        @CategoryID
        ,@MemberID
        ,@StatusID
        ,@Name
        ,@Street
        ,@Street2
        ,@City
        ,@State
        ,@Zip
        ,@Phone
        ,@Description
        ,@ExpiresOn)
END
    ", CommandType.Text,
                    DB.CreateParameter("@BusinessID", input.ID.ToInt()),
                    DB.CreateParameter("@CategoryID", input.CategoryID.ToInt()),
                    DB.CreateParameter("@MemberID", input.MemberID.ToInt()),
                    DB.CreateParameter("@StatusID", input.StatusID.ToInt()),
                    DB.CreateParameter("@Name", input.Name.ToNString()),
                    DB.CreateParameter("@Street", input.Street.ToNString()),
                    DB.CreateParameter("@Street2", input.Street2.ToNString()),
                    DB.CreateParameter("@City", input.City.ToNString()),
                    DB.CreateParameter("@State", input.State.ToNString()),
                    DB.CreateParameter("@Zip", input.Zip.ToNString()),
                    DB.CreateParameter("@Phone", input.Phone.ToNString()),
                    DB.CreateParameter("@Description", input.Description.ToNString()),
                    DB.CreateParameter("@ExpiresOn", input.ExpiresOn.ToDateTime()));
        }
    }
}
